/**
 * 
 */
package dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import exception.DataAccessException;

import service.SeniorAdminQueryHelper;
import utils.DBUtils;

import model.Administrator;
import model.User;

/**
 * @ClassName: SeniorAdminDaoImpl
 * @Description:
 * @author Hongming Wu
 * @date 2013-12-6
 * 
 */
public class SeniorAdminDaoImpl implements SeniorAdminDao {

	// User
	private static final String CREATE_USER = "insert into user(user_Id,user_name,user_pwd,title,access_id,expire_date,status) values(?,?,?,?,?,?,?)";
	private static final String UPDATE_USER = "update user set title=?,access_id=?,expire_date=?,status=? where user_id=?";
	private static final String DELETE_USER = "delete from user where user_id=?";
	private static final String GET_USER_BYID = "select * from user where user_id=?";
	// Admin
	private static final String GET_ADMIN_BYNAME = "select * from administrator where admin_username=?";

	@Override
	public void createUser(User user) {
		String userid = countUsers();
		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;

		try {
			pstmt = conn.prepareStatement(CREATE_USER);
			pstmt.setString(1, userid);
			pstmt.setString(2, user.getUser_Name());
			pstmt.setString(3, user.getUser_Pwd());
			pstmt.setString(4, user.getTitle());
			pstmt.setInt(5, user.getAccess_ID());
			pstmt.setString(6, user.getExpire_Date());
			pstmt.setInt(7, user.getStatus());
			pstmt.executeUpdate();
			System.out.println("Create a new user " + userid);
		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, null);
		}
	}

	@Override
	public void updateUser(User user) {
		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;

		try {
			pstmt = conn.prepareStatement(UPDATE_USER);
			pstmt.setString(1, user.getTitle());
			pstmt.setInt(2, user.getAccess_ID());
			pstmt.setString(3, user.getExpire_Date());
			pstmt.setInt(4, user.getStatus());
			pstmt.setString(5, user.getUser_ID());
			pstmt.executeUpdate();
			System.out.println("Update user " + user.getUser_ID());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, null);
		}

	}

	@Override
	public void deleteUser(String userID) {
		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;

		try {
			pstmt = conn.prepareStatement(DELETE_USER);
			pstmt.setString(1, userID);
			pstmt.executeUpdate();
			System.out.println("Delete user.");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, null);
		}
	}

	@Override
	public User getUserByID(String userID) {
		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		User user = null;

		try {
			pstmt = conn.prepareStatement(GET_USER_BYID);
			pstmt.setString(1, userID);
			rset = pstmt.executeQuery();

			if (rset.next()) {
				user = new User();
				user.setUser_ID(userID);
				user.setUser_Name(rset.getString("user_name"));
				user.setTitle(rset.getString("title"));
				user.setAccess_ID(rset.getInt("access_id"));
				user.setExpire_Date(rset.getString("expire_Date"));
				user.setStatus(rset.getInt("status"));
				user.setUser_Pwd(rset.getString("user_pwd"));
				System.out.println("Get user : " + userID);
			} else {
				throw new DataAccessException("User " + userID
						+ " is not existed");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, rset);
		}

		return user;
	}

	private String generateSQL(SeniorAdminQueryHelper helper) {
		String baseSQL = "select * from user where 1=1 ";
		if (helper != null) {
			if (StringUtils.isNotEmpty(helper.getUserID())) {
				baseSQL += " and user_id= '" + helper.getUserID() + "'";
			}
			if (StringUtils.isNotEmpty(helper.getUserName())) {
				baseSQL += " and user_name= '" + helper.getUserName() + "'";
			}
			if (helper.getAccessID() != null) {
				baseSQL += " and access_id=" + helper.getAccessID();
			}

		}
		baseSQL += " order by user_id desc";
		System.out.println("Generate SQL : " + baseSQL);
		return baseSQL;
	}

	@Override
	public Integer countUsers(SeniorAdminQueryHelper helper) {
		String sql = this.generateSQL(helper);

		sql = sql.replace("*", "count(*) user_count");
		sql = sql.substring(0, sql.indexOf("order")).trim();

		System.out.println("countUsers: " + sql);

		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		int userCount = 0;

		try {
			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			if (rset.next()) {
				userCount = rset.getInt("user_count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, rset);
		}
		return userCount;
	}

	@Override
	public List<User> getScopedUser(SeniorAdminQueryHelper helper, int begin,
			int end) {
		String sql = this.generateSQL(helper);
		sql += " limit ?,?";
		System.out.println("get scoped users:" + sql + "," + begin + "," + end);

		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		List<User> userList = null;
		int n = countUsers(helper);
		try {

			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, begin-1);
			pstmt.setInt(2, 3);
			rset = pstmt.executeQuery();
			userList = new ArrayList<User>();

			while (rset.next()) {
				User user = new User();
				user.setUser_ID(rset.getString("user_id"));
				user.setUser_Name(rset.getString("user_name"));
				user.setAccess_ID(rset.getInt("access_id"));
				user.setBirth(rset.getString("birth"));
				user.setExpire_Date(rset.getString("expire_Date"));
				user.setFirst_Name(rset.getString("first_name"));
				user.setLast_Name(rset.getString("last_name"));
				user.setStatus(rset.getInt("status"));
				user.setTitle(rset.getString("title"));
				user.setUser_Gender(rset.getString("user_gender"));
				user.setUser_Tel(rset.getString("user_tel"));
				user.setUser_Email(rset.getString("user_email"));
				// user.setPhoto(photo);
				// user.setQR_Code(qR_Code);
				userList.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, rset);
		}
		return userList;
	}

	@Override
	public Administrator getAdminByName(String userName) {
		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		Administrator admin = null;

		try {
			pstmt = conn.prepareStatement(GET_ADMIN_BYNAME);
			pstmt.setString(1, userName);
			rset = pstmt.executeQuery();

			if (rset.next()) {
				admin = new Administrator();
				admin.setAdmin_ID(rset.getString("admin_id"));
				admin.setAdmin_Username(userName);
				admin.setAdmin_Pwd(rset.getString("admin_pwd"));
				admin.setAdmin_Title(rset.getString("admin_title"));
				System.out.println("Get administrator : " + userName);
			} else {
				throw new DataAccessException("Administrator " + userName
						+ " is not existed");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, rset);
		}
		return admin;
	}

	@Override
	public String countUsers() {
		Connection conn = DBUtils.getInstance().getConn();
		PreparedStatement pstmt = null;
		ResultSet rset = null;

		String userid = "";
		try {
			pstmt = conn.prepareStatement("select max(user_id) max from user");
			rset = pstmt.executeQuery();

			if (rset.next()) {

				String maxid = rset.getString("max");
				maxid = maxid.substring(1, 6);
				int max = (Integer.parseInt(maxid) + 1);
				userid = "U" + max;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().ReleaseRes(null, pstmt, rset);
		}
		return userid;
	}

}
